import pymysql


def readData(db, redballnum):
	try:
		# print('START')
		sql = 'SELECT %s FROM %s' % (redballnum, db)
		# print(sql)
		conn = pymysql.connect(host='localhost', user='root', passwd="*******", db="lottery", port=3306,
		                       charset='utf8')
		cur = conn.cursor()
		cur.execute(sql)
		# print("ok")
		redball1list = []
		# print(cur.fetchone())
		for i in cur.fetchall():
			redball1list.append(i)
		length = cur.rowcount
		# print(length)
		return redball1list, length
		conn.commit()
		cur.close()
		conn.close()
	except Exception as e:
		with open('db_fail.txt', 'a')as f:
			f.write('%s    %s\n' % (db, e))


def calculateTheNumber(ball):
	number = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
	          30, 31, 32, 33]
	probability = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
	for i in ball:
		ix = int(i[0])
		# print(ix)
		for b in range(1, 34):
			# print(b)
			# print(number[b-1])
			if number[b - 1] == ix:
				probability[b - 1] += 1
		# print("%s次数%s" % (b, probability[b - 1]))
	return probability


def calculateTheProbability(probability, length):
	probabilityNum = [(0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0),
	                  (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0),
	                  (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0), (0, 0)]
	for b in range(1, 34):
		probabilityNum[b - 1] = (b, probability[b - 1] * 1.0 / length)
	result = sorted(probabilityNum, key=lambda x: x[1], reverse=True)
	print(result)

	re = [x[1] for x in result]
	reb = [x[0] for x in result]
	for i in range(0, 6):
		print(u'出现概率最高的6个球号码为：%d概率为%f' % (reb[i], re[i]))
	for i in range(27, 33):
		print(u'出现概率最低的6个球号码为：%d概率为%f' % (reb[i], re[i]))


# def calculateTheProbability(redball,length):
# 	number = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33]
# 	probability = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0]
# 	probabilityNum = [(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0)]
# 	for i in redball:
# 		ix = int(i[0])
# 		# print(ix)
# 		for b in range(1,34):
# 			# print(b)
# 			# print(number[b-1])
# 			if  number[b-1] == ix:
# 				probability[b-1] +=1
# 				print("%s次数%s" %(b,probability[b-1]))
# 				probabilityNum[ix-1] = (b,probability[b-1]*1.0/length)
# 	result = sorted(probabilityNum, key=lambda x: x[1], reverse=True)
# 	for i in range(1, 7):
# 		re = [x[1] for x in result]
# 		reb = [x[0] for x in result]
# 		print(u'出现概率最高的前六个球号码为：%d概率为%f' % (reb[i], re[i]))

if __name__ == '__main__':
	probability = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
	for i in range(1, 7):
		redballnum = 'redball%s' % i
		redball, length = readData('doublecolordata', redballnum)
		a = []
		a = calculateTheNumber(redball)
		probability = [a[c] + probability[c] for c in range(0, len(a))]
	print(probability)
	calculateTheProbability(probability, length)
	blueballchar = 'blueball'
	bluball, length2 = readData('doublecolordata', blueballchar)
	probability2 = calculateTheNumber(bluball)
	calculateTheProbability(probability2, length2)
